﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-08-21>
-- Description:	<获取指定客户的呼叫记录>
-- =============================================
CREATE PROCEDURE [dbo].[proc_InOutSide_CallList]
	@Id int
AS
BEGIN
	Declare @ProtectDate Datetime,@CusId uniqueidentifier

	--取该客户的起始保护日期
	Select @CusId=CusId,@ProtectDate=InSide_ProtectDate From InOutSide Where Id=@Id
	If @ProtectDate Is Null Or @CusId Is Null
	Begin
		Select -2 As ReturnValue
		Return		--该客户不存在
	End

	--该客户所有的联系电话
	;WITH List1 As(
		Select Mobile As Tel
		From ContactPerson
		Where CustomerId=@CusId
		And Mobile<>'' And (Mobile Is Not Null)
		Union
		Select OfficeTel As Tel
		From ContactPerson
		Where CustomerId=@CusId
		And OfficeTel<>'' And (OfficeTel Is Not Null)
		Union
		Select HomeTel As Tel
		From ContactPerson
		Where CustomerId=@CusId
		And HomeTel<>'' And (HomeTel Is Not Null)
	),--取该客户保护日期开始的所有呼叫记录(由从保护日期起的条件改为提取最后3次)
	List As (--Select Id,
Select Top 3 Id,
		GId,
		ZuoxiID,
		[Type],
		r.Tel,
		IsNull(StartDate,'1900-01-01') AS StartDate,
		IsNull(EndDate,'1900-01-01') AS EndDate,
		IsNull(DateDiff(ss,StartDate,EndDate),0) As Seconds, 
		TxtFileName,
		[FileName],
		EName,
		CompanyId,
		IsNull((Select DName From Department Where Id=r.DepartmentId),'---') As Department
		From CallCenter_Record r,List1 l
-- 
--		Where EndDate>=@ProtectDate And r.Tel=l.Tel
		Where r.Tel=l.Tel
Order By EndDate Desc
	)

	Select * From List	-- Order By Id Desc
END
